var express = require('express');
var app = express();
var path = require('path');
var bodyParser = require('body-parser');
app.use(bodyParser.json());
app.use(bodyParser.urlencoded({ extended: true }));
app.use(express.static('public'))
//加载数据驱动
var sqlite3 = require('sqlite3').verbose();
var db = new sqlite3.Database('E:/yosang/score.sqlite');
var myres={
		status:0,
		resultarr:[]
	}

app.get('/', function (req, res) {
	res.sendFile(path.join(__dirname,'/index.html'))
});
//获取所有人的工分总和
app.get('/getdata', function (req, res) {
	
	//数据库操作
	db.serialize(function() {
	 db.all('select sum(userscore) as userallscore,* from (select * from user inner join (select mytest.cscore*taskuser.percent*0.01 as userscore,userid from taskuser inner join (select task.score*tasktree.percent*0.01 as cscore ,* from task inner join tasktree on task.id=tasktree.pid) as mytest on mytest.cid = taskuser.taskid)as mytest2 on mytest2.userid=user.id) group by userid;', function(err, rows) {
	 	myres.status=1;
 		myres.resultarr=rows;
 		res.send(myres);
	  });
	});
   
});
//更新task表
app.post('/reflash', function (req, res) {
	/**
	 *数据库操作
	 *返回值：{status,resultarr}
	 *status1:更新成功
	 *status2：tasktree表中百分比有错误
	 *status3：taskuser表中百分比有错误
	 *status4：taskuser出现父节点
	 *resultarr:记录出错的地方,或者成功时返回
	 */
	function finderr1() {
    	var err1 = new Promise(function (resolve,reject) {
	    	db.all('select pid from tasktree group by pid having sum(percent)>100.1',function (err,rows) {
	    		resolve(rows);
	    	});
	    })
	    return err1;
    }
    function finderr2() {
    	console.log('finderr2')
    	var err2 = new Promise(function (resolve,reject) {
	    	db.all('select taskid as pid from taskuser group by taskid having sum(percent)>100.1',function (err,rows) {
	    		resolve(rows);
	    	});
	    })
	    return err2;
    }
    function finderr3() {
    	console.log('finderr3')
    	var err3 = new Promise(function (resolve,reject) {
	    	db.all('select distinct taskid as pid from taskuser where taskid in (select distinct pid from tasktree where pid>=0)',function (err,rows) {
	    		resolve(rows);
	    	});
	    })
	    return err3;
    }
    function updatescore1() {
    	var updatedata = new Promise(function (resolve,reject) {
	    	db.run('update task set score=(select cscore from (select task.score*tasktree.percent*0.01 as cscore ,* from task inner join tasktree on task.id=tasktree.pid) as mytest where mytest.cid=task.id ) where id  in (select cid from tasktree)',function (err) {
	    		resolve(err);
	    	});
	    })
	    return updatedata;
    }
    function updatescore2() {
    	var updatedata = new Promise(function (resolve,reject) {
	    	db.run('update taskuser set score=(select userscore from user inner join (select mytest.cscore*taskuser.percent*0.01 as userscore,userid,taskuser.id as taskuserid from taskuser inner join (select task.score*tasktree.percent*0.01 as cscore ,* from task inner join tasktree on task.id=tasktree.pid) as mytest on mytest.cid = taskuser.taskid)as mytest2 on mytest2.userid=user.id where taskuser.id=taskuserid)',function (err,rows) {
	    		resolve(err);
	    	});
	    })
	    return updatedata;
    }
	db.serialize(function() {
	    finderr1()
	    .then(function (rows) {
	    	if (rows.length==0) {
		 		return finderr2();
		 	}else{
		 		myres.status=2;
		 		myres.resultarr=rows;
		 		res.send(myres);
		 		reject();
		 	}
	    })
	    .then(function (rows) {
	    	if (rows.length==0) {
		 		return finderr3();
		 	}else{
		 		myres.status=3;
		 		myres.resultarr=rows;
		 		res.send(myres);
		 		 reject();
		 	}
	    },function () {
	    	
	    })
        .then(function (rows) {
    	   if (rows.length==0) {
    		 updatescore1();
		 	}else{
		 		myres.status=4;
		 		myres.resultarr=rows;
		 		res.send(myres);
		 		 reject()
		 	}
	    },function () {
	    	
	    })
	    .then(function (result) {
	    	if (!result) {
	    		updatescore2();
	    	}
	    })
	    .then(function (result) {
	    	if (!result) {
	    		db.all("select sum(userscore) as userallscore,userid,name,class,id from (select * from user inner join (select mytest.cscore*taskuser.percent*0.01 as userscore,userid,taskuser.time from taskuser inner join (select task.score*tasktree.percent*0.01 as cscore ,* from task inner join tasktree on task.id=tasktree.pid) as mytest on mytest.cid = taskuser.taskid where taskuser.time >= '"+req.body.starttime+"' and taskuser.time <= '"+req.body.starttime +"')as mytest2 on mytest2.userid=user.id) group by userid",function (err,rows) {
				    if (rows) {
				    	myres.status=1;
				 		myres.resultarr=rows;
				 		res.send(myres);
				    }
				});
	    	}
	    })
	    

	});
   
});
app.get('/test',function (req,res) {
	db.serialize(function() {
		 db.all("select * from task limit 100",function(rows,err) {
		 	console.log(err);
		 })
		  db.all("select * from task limit 100,100",function(rows,err) {
		 	console.log(err);
		 })
		 // db.run("INSERT INTO task (name, score, time,reference) VALUES ('ya2', 1, '2018-08-30','wa')",function(err) {
		 // 	console.log(err);
		 // })
		 // db.run("INSERT INTO task (name, score, time,reference) VALUES ('ya2', 1, '2018-08-30','wa')",function(err) {
		 // 	console.log(err);
		 // })
	})
})
var server = app.listen(3000, function () {
  var host = server.address().address;
  var port = server.address().port;

  console.log('Example app listening at http://%s:%s', host, port);
});


	
